Desertation

Table of contents

• Actuality
• Purpose of work
• Scientific novelty
• Architecture «client-server»
• Microsoft Sql Server
  • Client-server system of SQL Server
  • Support of the extended memory
  • Frequent copies of SQL Server
  • Operations on support of database
• Resources
• Replication
• Tools of development
• Architecture of Interbase
• Multiversion architecture
• High reliability
• Triggers
• Kept procedures
• Functions (UDF)
• Lightness of management and service
• Resources
• Automatic treatment of two-phase confirmation of transactions(2PC)
• ANSI SQL-92
• Tools of development:
• Limitations of database:
• System requirements

Actuality

     At present exists such servers of databases, as Interbase, Microsoft SQL Server, Oracle, Informix, Sybase, DB2. The choice of the control system by databases is one of important stages at development of appendixes of databases.

Purpose of work

     Research of productivity of the control system of database is the purpose of work. Comparisons of servers of databases were made for this purpose.

Scientific novelty

     A scientific novelty consists of conducting of tests and analysis of productivity of different servers of databases, estimation of quickness of work.

Architecture «client-server»

     Increase of complication of tasks, appearance of the personal computers and local areas networks became pre-conditions of appearance of new architecture «file is server ». This architecture of databases with network access supposes setting of one of computers of network as the dedicaded server which the files of database will be kept on. In accordance with the queries of users files with «file - server» is passed on the work stations of users, where and basic part of the data processing is carried out. A central server executes the role of depository of files mainly only, not participating in the data processing. A database as the set of files is on the hard disk of the specially selected computer (file server). There is a local network consisting of clients computers, on each of which SCBD and appendix is set for work with DB (pic. 1)

Picture 1 - Architecture«Client-Server»(Animation)

Microsoft Sql Server

     Microsoft SQL Server is the relation database management system. At relation databases information is kept in tables. Associate information can form group in tables, in addition, can be set also and relations between tables. Users get access to information on a server through applications, and administrators, executing the tasks of configuring, administration and support of database, make direct access to the server.

Client-server system of SQL Server

     Client-server System of SQL Server can have the two-tier setup or three-tier setup. Regardless of variant options, software and databases of SQL Server will be stirred on a central computer which is named the server of database. Users work on separate computers which are named clients. Access of users to the server of database is made at by the help of applications from their computers-clients (in the two-tier systems), or through the applications, executed on the computer specially intended for this purpose, and which is named the application server (in the three-tier systems) (fig. 1.1).

Picture 1.1 - two-tier

Picture 1.2 – three-tie

Support of the extended memory

     SQL Server 2000 Entreprise Edition can use API (by the interface of the application programming) Windows 2000 Address Windowing Extension (AWE) for support of large addresses spaces. On servers under the management of Windows 2000 Advanced Server SQL Server supports memory to 8 Gb, and on servers under the management of Windows 2000 Datacenter - about 64 Gb. Support of AWE is present only in these two operating systems

Frequent copies of SQL Server

     In SQL Server 2000 execution of a few copies of SQL Server is assumed on one computer. Every copy has the own system and users databases. Applications can unite with the copies of SQL Server just as they would unite with the copies of SQL Server, working on other computer. It is possible to apply the copies of SQL Server for the groupment of the typical applied tasks, so that the separate groups of tasks were served by the own copies of SQL Server.

Operations on support of database

     To the improvements of SQL Server 2000 that for some operations of support of database, executable by administrators, behaves also, speed of their execution rose and the comfort of work became better. To these by the improvements the increase of speed of the differential reserve copying (differential backup), and parallel scanning, belong with verification of co-ordination of database (DBCC). References data integrity Through two new suggestions - ON UPDATE and ON DELETE - it is possible to set the conduct of SQL Server at the change of column in a table to which an external cursor (foreign key) refers in other table. Suggestions of ON UPDATE and ON DELETE can be used in the statements of CREATE TABLE and ALTER TABLE. These suggestions have options of CASCADING and NO ACTION. CASCADING means with ON DELETE, that if from the specified table a row is deleted, this delete will be a «cascade», will render also influence on the table of external cursors. Like, CASCADING means with ON UPDATE, that the update applied to the specified column of information in a paternal table will be used by a «cascade», so that the table of external cursors will brush up the same way. In SQL Server 2000 a few new improvements appeared for indexation. The following possibilities provide large flexibility at indexation:

• to create indexes for the calculated columns;
• to set the sequence of creation of indexes, both increasing and decreasing;
• to set, whether an index must to be created with the use of the parallel scanning or sorting.

Resources

Microsoft SQL Server requires the presences of 60M on a disk for setting and 16MB RAM. Every user occupies on a 48K memory. PerformAnce Analyzer In Enterprise Manager a new tool appeared is a PerformAnce Analyzer (An analyzer is productivity).This tool serves for the data capture about productivity for a separate database or for all databases. These tracings are kept in a table, and on their basis a «cube OLAP» (OLAP - online Analytical processing, analytical treatment in real time). For viewing and data analysis about productivity it is possible to use applications able to read the cube of OLAP.

Replication

     A few improvements of replication appeared In SQL Server 2000. One of them is new alternative for option of immediate update of subscriber. This new option queued update is named (update, organized in a turn). Option of queued update is intended specially for replication of snapshot and for replication of transactions. The new improvements appeared for replication by confluence:

• new mechanisms of permission of conflicts (conflict resolvers);
• option for interactive permission of conflicts;
• vertical filtration for confluence of conflicts;
• possibility to add to the dynamic filters the functions determined by users;
• automatic control by the grades of authentication at subscribers;
• possibility during synchronization of information to have alternative publishers.

Tools of development

     Are the basic tools of development following:

• compiling programming languages: Delphi, Borland C++, Borland C++ Builder, MS C/Visual C++;
• MS Visual Basic

Architecture of Interbase

     There are two as architecture of Interbase: Classic and SuperServer, which considerably differ from each other by the methods of work with clients, organization of cooperation of own models and even composition of the modules included in certain realization of architecture.

Multiversion architecture

     Interbase is first SCBD multiversion architecture is realized in which. Exactly multiversion architecture allows to organize cooperation of users so that reading users do not lock writing, and also enables to be very quickly restored after failures in a database and to renounce conduct of protocol of transactions (transaction log).
     The basic idea of multiversion architecture consists of that all changes conducted above concrete records is made not with a record, and above its version.

High reliability

     Interbase is the first, offering conception of active database. An active database contains notifications about events, kept procedures, triggers, functions and filters of the fields determined by an user, as BLOb for automation of processes what is going on a server.

Trigger

     Triggers keeps and execute logic of the data processing on a server. Thus every application using corporate information automatically uses this logic. Triggers of Interbase automatizes a response on events on a server, and is often used for the background check at the insertion, change and delete of records in tables

Kept procedures

     The kept procedures in Interbase allow to realize considerable part of logic of application at the level of database and, thus, to promote productivity of the data processing, executing it on a server.

Functions (UDF)

     Functions of UDF provide possibility of expansion of SQL-server by the Interbase external functions of any complication, written in compiling programming languages. UDF can make the special treatment of date and time, numbers, lines, content of the BLOb-fields, and also to cause applications of the operating system. Lightness of management and service

Lightness of management and service

     Interbase supports 16- and 32-bit GUI Microsoft Windows for simplification of administration, monitoring and debugging from a client computer.

Resources

     For setting 10 Mb is required minimum on a disk (greater part is occupied by directory files and examples of programing) and the least of main memory, sufficient for work of the operating system.

Automatic treatment of two-phase confirmation of transactions(2PC)

     Interbase allows easily to work with multiservers transactions, and is first commercial SCBD realizing protocol of 2PC. Thus treatment of transactions is executed on the chart of two-phase confirmation of transactions, that guarantees data integrity without writing of additional code. As soon as transaction is executed above two and more by the servers of databases, Interbase at first checks up readiness of all servers to completion of transaction, and then sends the command of final completion of transactions. Renewal of the transactions uncompleted after the first phase is executed due to the mechanism of plural generations of records

ANSI SQL-92

     Interbase provides compatibility with a standard SQL-92 (complete accordance of entry level). It reduces expenses on teaching at mastering of Interbase SQL Server, as the programming language of databases corresponds to the opened industrial standard.

Tools of development:

    

• PowerPlay, PowerHouse 4GL, and Impromptu from Cognos Corp;
• JAM for IB Database from JYACC, Inc;
• Delphi and Delphi Client/Server;
• Borland Database Engine and SQL Links;
• Visual dBASE with Borland SQL Links;
• Paradox for Windows with Borland SQL Links;
• Possibility of the use of powerful native API from a compiling programming language: Delphi, Borland C++, Borland C++ Builder, MS C/Visual C++.

Limitations of database:

    

• maximal amount of records in one table: 2 milliards;
• maximal size of table: it is limited to the resources of the system;
• amount of databases in the system: it is limited to the resources of the system;
• amount of active users: it is limited to the resources of the system;
• amount of tables in a database: 64K;
• amount of indexes in a database: 64K;
• maximal size of record (not including BLOb): 64Kb.

System requirements

     The minimum of main memory (necessary minimum for the operating system) and disk space depends on the operating system of concrete platform
     Network protocol: for all platforms of TCP/IP, other protocols depending on a concrete platform.

literature

1. Ковязин А.Н., Востриков С.М. Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase, Firebird, Yaffil. – М.:Питер, 2005. – 496 с.
2. М.Ф.Гарсиа, Дж.Рединг, Э.Уолен, С.А.ДеЛюк Microsoft SQL Server 2000. Справочник администратора. – М.: СП Эком, 2004. – 976 с.
3. Перевод: Александра Гладченко 2002г ,По материалам статьи Michael Otey Performance Counters Семь наиболее полезных счётчиков эффективности.
   http://www.sql.ru/articles/mssql/02111903PerformanceCounters.shtml
4. Кузьменко Дмитрий, 22 октября 1998.Исправлено 26 сентября 2000.-Тест вставки записей.
   http://ibase.ru/devinfo/instest.htm
   
5. Microsoft - Правило оптимизация производительность SQL Server 7.0 :Принцип и средство оптимизация производительности
   http://www.microsoft.com/france/technet/produits/sql/7.0/sql7pt_1.mspx#ERE
   
6. Санкт-Петербургский Государственный институт точной механики и оптики (технический университет) Кафедра вычислительной техники В.В.Кириллов, Г.Ю.Громов - Структуризированный язык запросов (SQL)
   http://www.cs.ifmo.ru/education/documentation/sql_kg/index.shtml
   
7. Иван Фролков - Краткий справочник по Transact SQL
   http://www.sql.ru/docs/mssql/tsql_ref/index.shtml
   
8. Перевод: Александра Гладченко 2002г. - Мониторинг эффективности MS SQL Server. Практические рекомендации
   http://www.sql.ru/articles/mssql/02021801MonitoringPerformanceSQLServer.shtml
   
9. Алексей Лукин - SQL. С самого начала
   http://cooler.irk.ru/ruby/r_dbas1.html
10. http://ibase.ru/
    
11. Дмитрий Попов - Опыт работы с InterBase
    http://ibase.ru/dpopov/index.html
    
12. Использование языка SQL, создание клиент-серверных систем. Конференция по MSSQL, Oracle, Interbase, MySQL. Полезные ссылки, документация, рекомендации по разработке информационных систем,
    http://sql.ru/
    
13. Jean-Claude SOHM - Основные понятые СУБД
    http://cerig.efpg.inpg.fr/tutoriel/bases-de-donnees/sommaire.htm